package by.semerikov.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import by.semerikov.entity.PersonEntity;
import by.semerikov.exceptions.DAOException;

import com.mysql.jdbc.PreparedStatement;

public class PersonDAO implements IPersonDAO {

	private Connection connection;

	PreparedStatement insertStatement;

	public PersonDAO() {
	}

	public PersonDAO(Connection con) {
		this.connection = con;
	}

	@Override
	public void create(PersonEntity object) throws DAOException {
		try {
			PreparedStatement preparedState = (PreparedStatement) connection
					.prepareStatement("insert into transportsystem.personal (name,surname,post,login,password) values(?,?,?,?,?)");
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getSurname());
			preparedState.setString(3, object.getPost());
			preparedState.setString(4, object.getLogin());
			preparedState.setString(5, object.getPassword());
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new DAOException("exception.dao.person.create",e);
		}

	}

	@Override
	public List<PersonEntity> read(PersonEntity object) throws DAOException {
		ArrayList<PersonEntity> newList = new ArrayList<PersonEntity>();
		try {
			PreparedStatement preparedState = doReadPreparedStatement(object,
					"SELECT * FROM transportsystem.personal ");
			ResultSet resultset = preparedState.executeQuery();
			while (resultset.next()) {
				newList.add(new PersonEntity(Integer.parseInt(resultset
						.getString("id")), resultset.getString("name"),
						resultset.getString("surname"), resultset
								.getString("post"), resultset
								.getString("login"), resultset
								.getString("password")));
			}
			return newList;
		} catch (Exception e) {
			throw new DAOException("exception.dao.person.read",e);
		}
	}

	private PreparedStatement doReadPreparedStatement(PersonEntity object,
			String begin) throws SQLException {
		String newStr = begin;
		Map<String, Integer> map = new HashMap<String, Integer>();
		int number = 1;
		if (object.getId() != null) {
			newStr += "where  id = ?";
			map.put("id", number);
			number++;
		}
		if (object.getName() != null) {
			if (number == 1)
				newStr += " where ";
			if (number != 1)
				newStr += " And ";
			newStr += " name Like ?";
			map.put("name", number);
			number++;
		}
		if (object.getSurname() != null) {
			if (number == 1)
				newStr += " where ";
			if (number != 1)
				newStr += " And ";
			newStr += " surname Like ?";
			map.put("surname", number);
			number++;
		}
		if (object.getPost() != null) {
			if (number == 1)
				newStr += " where ";
			if (number != 1)
				newStr += " And ";
			newStr += " post = ?";
			map.put("post", number);
			number++;
		}
		if (object.getLogin() != null) {
			if (number == 1)
				newStr += " where ";
			if (number != 1)
				newStr += " And ";
			newStr += " login = ?";
			map.put("login", number);
			number++;
		}
		if (object.getPassword() != null) {
			if (number == 1)
				newStr += " where ";
			if (number != 1)
				newStr += " And ";
			newStr += " password = ?";
			map.put("password", number);
			number++;
		}
		PreparedStatement newPS = (PreparedStatement) connection
				.prepareStatement(newStr);
		if (map.get("id") != null) {
			newPS.setInt(map.get("id"), object.getId());
		}
		if (map.get("name") != null) {
			newPS.setString(map.get("name"), "%" + object.getName() + "%");
		}
		if (map.get("surname") != null) {
			newPS.setString(map.get("surname"), "%" + object.getSurname() + "%");
		}
		if (map.get("post") != null) {
			newPS.setString(map.get("post"), object.getPost());
		}
		if (map.get("login") != null) {
			newPS.setString(map.get("login"), object.getLogin());
		}
		if (map.get("password") != null) {
			newPS.setString(map.get("password"), object.getPassword());
		}
		return newPS;
	}

	@Override
	public void update(PersonEntity object) throws DAOException {
		try {

			PreparedStatement preparedState = (PreparedStatement) connection
					.prepareStatement("UPDATE transportsystem.personal SET name=? , surname=? , post=? , login=? , password=? WHERE id=?");
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getSurname());
			preparedState.setString(3, object.getPost());
			preparedState.setString(4, object.getLogin());
			preparedState.setString(5, object.getPassword());
			preparedState.setInt(6, object.getId());
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new DAOException("exception.dao.person.update",e);
		}

	}

	@Override
	public void delete(PersonEntity object) throws DAOException {
		try {

			PreparedStatement preparedState = doReadPreparedStatement(object,
					"DELETE FROM transportsystem.personal ");
			preparedState.executeUpdate();

		} catch (Exception e) {
			throw new DAOException("exception.dao.person.delete",e);
		}

	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection con) {
		this.connection = con;
	}

}
